<?php

class Application_Model_PendingRmaMapper {
	
	public function fetchAmountByReasoncodeAndCustomer()
	{
		$db = Zend_Db_Table::getDefaultAdapter();
		
		$query1 = $db->select()
					->from(array('p' => 'bis.tbl_pendingRma'), array('customerName' => 'c.customerName',
																	'defectiveAmount' => 'SUM(CASE WHEN p.returnType=\'DE Defective Return\' THEN p.pendingQty*p.sellingPrice ELSE 0 END)',
																	'balanceAmount' => 'SUM(CASE WHEN p.returnType=\'DE Mint Return\' THEN p.pendingQty*p.sellingPrice ELSE 0 END)',
																	'missshipmentAmount' => 'SUM(CASE WHEN p.returnType=\'DE Mis-Shipment Return\' THEN p.pendingQty*p.sellingPrice ELSE 0 END)',
																	'refusalAmount' => 'SUM(CASE WHEN p.returnType=\'DE Refusal\' THEN p.pendingQty*p.sellingPrice ELSE 0 END)',
																	'totalAmount' => 'SUM(p.pendingQty*p.sellingPrice)'))
					->joinLeft(array('c' => 'bis.tbl_customer'), 'c.customerNumber = p.customerNumber', null)
					->where('p.rmaStatus = \'ENTERED\'')
					->group('c.customerName');
		
		$query2 = $db->select()
					->from(array('p' => 'bis.tbl_pendingRma'), array('customerName' => '(\'TOTALS\')',
																	'defectiveAmount' => 'SUM(CASE WHEN p.returnType=\'DE Defective Return\' THEN p.pendingQty*p.sellingPrice ELSE 0 END)',
																	'balanceAmount' => 'SUM(CASE WHEN p.returnType=\'DE Mint Return\' THEN p.pendingQty*p.sellingPrice ELSE 0 END)',
																	'missshipmentAmount' => 'SUM(CASE WHEN p.returnType=\'DE Mis-Shipment Return\' THEN p.pendingQty*p.sellingPrice ELSE 0 END)',
																	'refusalAmount' => 'SUM(CASE WHEN p.returnType=\'DE Refusal\' THEN p.pendingQty*p.sellingPrice ELSE 0 END)',
																	'totalAmount' => 'SUM(p.pendingQty*p.sellingPrice)'))
					->where('p.rmaStatus = \'ENTERED\'');
					
		$result = $db->select()->union(array($query1, $query2))->query()->fetchAll();
		
		return $result;
	}
	
	public function fetchQtyByReasoncodeAndDivision()
	{
		$db = Zend_Db_Table::getDefaultAdapter();
	
		$query1 = $db->select()
					->from(array('p' => 'bis.tbl_pendingRma'), array('divisionDesc' => 'i.divisionDesc',
																	'defectiveQty' => 'SUM(CASE WHEN p.returnType=\'DE Defective Return\' THEN p.pendingQty ELSE 0 END)',
																	'balanceQty' => 'SUM(CASE WHEN p.returnType=\'DE Mint Return\' THEN p.pendingQty ELSE 0 END)',
																	'missshipmentQty' => 'SUM(CASE WHEN p.returnType=\'DE Defective Return\' THEN p.pendingQty ELSE 0 END)',
																	'refusalQty' => 'SUM(CASE WHEN p.returnType=\'DE Defective Return\' THEN p.pendingQty ELSE 0 END)',
																	'totalQty' => 'SUM(p.pendingQty)'))
						->joinLeft(array('i' => 'bis.tbl_inventoryItem'), 'i.inventoryItem = p.inventoryItem', null)
						->where('p.rmaStatus = \'ENTERED\'')
						->group('i.divisionDesc');
					
		$query2 = $db->select()
					->from(array('p' => 'bis.tbl_pendingRma'), array('divisionDesc' => '(\'TOTALS\')',
																	'defectiveQty' => 'SUM(CASE WHEN p.returnType=\'DE Defective Return\' THEN p.pendingQty ELSE 0 END)',
																	'balanceQty' => 'SUM(CASE WHEN p.returnType=\'DE Mint Return\' THEN p.pendingQty ELSE 0 END)',
																	'missshipmentQty' => 'SUM(CASE WHEN p.returnType=\'DE Defective Return\' THEN p.pendingQty ELSE 0 END)',
																	'refusalQty' => 'SUM(CASE WHEN p.returnType=\'DE Defective Return\' THEN p.pendingQty ELSE 0 END)',
																	'totalQty' => 'SUM(p.pendingQty)'))
					->where('p.rmaStatus = \'ENTERED\'');
					
		$result = $db->select()->union(array($query1, $query2))->query()->fetchAll();
	
		return $result;
	}
	
	public function fetchPendingRma() {
		
		$db = Zend_Db_Table::getDefaultAdapter();
		
		$sqlString = "SELECT 
						s.salesrepname,
						SUM(CASE WHEN p.returnType='DE Defective Return' AND i.divisionDesc = 'DIV_CALL_OF_DUTY' THEN p.pendingQty*p.sellingPrice ELSE 0 END) AS 'defectiveCod',
						SUM(CASE WHEN p.returnType='DE Defective Return' AND i.divisionDesc = 'DIV_BLIZZARD' THEN p.pendingQty*p.sellingPrice ELSE 0 END) AS 'defectiveBlizz',
						SUM(CASE WHEN p.returnType='DE Defective Return' AND i.divisionDesc = 'DIV_SKYLANDERS' THEN p.pendingQty*p.sellingPrice ELSE 0 END) AS 'defectiveSky',
						SUM(CASE WHEN p.returnType='DE Defective Return' AND i.divisionDesc = 'DIV_AMP' THEN p.pendingQty*p.sellingPrice ELSE 0 END) AS 'defectiveAmp',
						SUM(CASE WHEN p.returnType='DE Defective Return' AND i.divisionDesc = 'DIV_LEGACY_3100' THEN p.pendingQty*p.sellingPrice ELSE 0 END) AS 'defectiveLegacy',
						SUM(CASE WHEN p.returnType='DE Defective Return' AND i.divisionDesc = 'DIV_HERO' THEN p.pendingQty*p.sellingPrice ELSE 0 END) AS 'defectiveHero',
						SUM(CASE WHEN p.returnType='DE Defective Return' AND i.divisionDesc = 'DIV_LUCAS' THEN p.pendingQty*p.sellingPrice ELSE 0 END) AS 'defectiveLucas',
					
						SUM(CASE WHEN p.returnType='DE Mint Return' AND i.divisionDesc = 'DIV_CALL_OF_DUTY' THEN p.pendingQty*p.sellingPrice ELSE 0 END) AS 'balanceCod',
						SUM(CASE WHEN p.returnType='DE Mint Return' AND i.divisionDesc = 'DIV_BLIZZARD' THEN p.pendingQty*p.sellingPrice ELSE 0 END) AS 'balanceBlizz',
						SUM(CASE WHEN p.returnType='DE Mint Return' AND i.divisionDesc = 'DIV_SKYLANDERS' THEN p.pendingQty*p.sellingPrice ELSE 0 END) AS 'balanceSky',
						SUM(CASE WHEN p.returnType='DE Mint Return' AND i.divisionDesc = 'DIV_AMP' THEN p.pendingQty*p.sellingPrice ELSE 0 END) AS 'balanceAmp',
						SUM(CASE WHEN p.returnType='DE Mint Return' AND i.divisionDesc = 'DIV_LEGACY_3100' THEN p.pendingQty*p.sellingPrice ELSE 0 END) AS 'balanceLegacy',
						SUM(CASE WHEN p.returnType='DE Mint Return' AND i.divisionDesc = 'DIV_HERO' THEN p.pendingQty*p.sellingPrice ELSE 0 END) AS 'balanceHero',
						SUM(CASE WHEN p.returnType='DE Mint Return' AND i.divisionDesc = 'DIV_LUCAS' THEN p.pendingQty*p.sellingPrice ELSE 0 END) AS 'balanceLucas',
					
						SUM(CASE WHEN p.returnType='DE Mis-Shipment Return' AND i.divisionDesc = 'DIV_CALL_OF_DUTY' THEN p.pendingQty*p.sellingPrice ELSE 0 END) AS 'missCod',
						SUM(CASE WHEN p.returnType='DE Mis-Shipment Return' AND i.divisionDesc = 'DIV_BLIZZARD' THEN p.pendingQty*p.sellingPrice ELSE 0 END) AS 'missBlizz',
						SUM(CASE WHEN p.returnType='DE Mis-Shipment Return' AND i.divisionDesc = 'DIV_SKYLANDERS' THEN p.pendingQty*p.sellingPrice ELSE 0 END) AS 'missSky',
						SUM(CASE WHEN p.returnType='DE Mis-Shipment Return' AND i.divisionDesc = 'DIV_AMP' THEN p.pendingQty*p.sellingPrice ELSE 0 END) AS 'missAmp',
						SUM(CASE WHEN p.returnType='DE Mis-Shipment Return' AND i.divisionDesc = 'DIV_LEGACY_3100' THEN p.pendingQty*p.sellingPrice ELSE 0 END) AS 'missLegacy',
						SUM(CASE WHEN p.returnType='DE Mis-Shipment Return' AND i.divisionDesc = 'DIV_HERO' THEN p.pendingQty*p.sellingPrice ELSE 0 END) AS 'missHero',
						SUM(CASE WHEN p.returnType='DE Mis-Shipment Return' AND i.divisionDesc = 'DIV_LUCAS' THEN p.pendingQty*p.sellingPrice ELSE 0 END) AS 'missLucas',
					
						SUM(CASE WHEN p.returnType='DE Refusal' AND i.divisionDesc = 'DIV_CALL_OF_DUTY' THEN p.pendingQty*p.sellingPrice ELSE 0 END) AS 'refusalCod',
						SUM(CASE WHEN p.returnType='DE Refusal' AND i.divisionDesc = 'DIV_BLIZZARD' THEN p.pendingQty*p.sellingPrice ELSE 0 END) AS 'refusalBlizz',
						SUM(CASE WHEN p.returnType='DE Refusal' AND i.divisionDesc = 'DIV_SKYLANDERS' THEN p.pendingQty*p.sellingPrice ELSE 0 END) AS 'refusalSky',
						SUM(CASE WHEN p.returnType='DE Refusal' AND i.divisionDesc = 'DIV_AMP' THEN p.pendingQty*p.sellingPrice ELSE 0 END) AS 'refusalAmp',
						SUM(CASE WHEN p.returnType='DE Refusal' AND i.divisionDesc = 'DIV_LEGACY_3100' THEN p.pendingQty*p.sellingPrice ELSE 0 END) AS 'refusalLegacy',
						SUM(CASE WHEN p.returnType='DE Refusal' AND i.divisionDesc = 'DIV_HERO' THEN p.pendingQty*p.sellingPrice ELSE 0 END) AS 'refusalHero',
						SUM(CASE WHEN p.returnType='DE Refusal' AND i.divisionDesc = 'DIV_LUCAS' THEN p.pendingQty*p.sellingPrice ELSE 0 END) AS 'refusalLucas',
					
						SUM(CASE WHEN i.divisionDesc = 'DIV_CALL_OF_DUTY' THEN p.pendingQty*p.sellingPrice ELSE 0 END) AS 'CodTotal',
						SUM(CASE WHEN i.divisionDesc = 'DIV_BLIZZARD' THEN p.pendingQty*p.sellingPrice ELSE 0 END) AS 'BlizzTotal',
						SUM(CASE WHEN i.divisionDesc = 'DIV_SKYLANDERS' THEN p.pendingQty*p.sellingPrice ELSE 0 END) AS 'SkyTotal',
						SUM(CASE WHEN i.divisionDesc = 'DIV_AMP' THEN p.pendingQty*p.sellingPrice ELSE 0 END) AS 'AmpTotal',
						SUM(CASE WHEN i.divisionDesc = 'DIV_LEGACY_3100' THEN p.pendingQty*p.sellingPrice ELSE 0 END) AS 'LegacyTotal',
						SUM(CASE WHEN i.divisionDesc = 'DIV_HERO' THEN p.pendingQty*p.sellingPrice ELSE 0 END) AS 'HeroTotal',
						SUM(CASE WHEN i.divisionDesc = 'DIV_LUCAS' THEN p.pendingQty*p.sellingPrice ELSE 0 END) AS 'LucasTotal',
						
						SUM(CASE WHEN p.returnType='DE Defective Return' THEN p.pendingQty*p.sellingPrice ELSE 0 END) AS 'defectiveTotal',
						SUM(CASE WHEN p.returnType='DE Mint Return' THEN p.pendingQty*p.sellingPrice ELSE 0 END) AS 'balanceTotal',
						SUM(CASE WHEN p.returnType='DE Mis-Shipment Return' THEN p.pendingQty*p.sellingPrice ELSE 0 END) AS 'missTotal',
						SUM(CASE WHEN p.returnType='DE Refusal' THEN p.pendingQty*p.sellingPrice ELSE 0 END) AS 'refusalTotal',
					
						SUM(p.pendingQty*p.sellingPrice) AS 'Total'
					
					
					FROM bis.tbl_pendingrma p
					
					LEFT JOIN bis.tbl_customer c On c.customerNumber = p.customerNumber
					LEFT JOIN bis.tbl_customer_salesrep cs ON cs.customer_partyNumber = c.partyNumber
					LEFT JOIN bis.tbl_salesrep s ON s.id = cs.salesrep_id
					LEFT JOIN bis.tbl_inventoryItem i ON i.inventoryItem = p.inventoryItem

					WHERE p.rmaStatus='ENTERED'
				
					GROUP BY s.salesrepname;";
		
		return $db->query($sqlString)->fetchAll();
		
	}
}